package com.syni.mdd.yhd.common.all.dao.jpa;

import com.syni.mdd.yhd.common.all.dao.BaseJPADao;
import com.syni.mdd.yhd.common.all.entity.BmsBusinessType;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;
import java.util.Set;

public interface BmsbusinessTypeDao extends BaseJPADao<BmsBusinessType,Integer> {
    BmsBusinessType findByTypeNameAndIsDelete(String typeName,Integer isDelete);

    List<BmsBusinessType> findByIsDelete(Integer isDelete);

    List<BmsBusinessType> findByIdInAndIsDelete(Set ids, Integer isDelete);

    /*@Query(value = "select br.bms_type_id from bms_dx_group_buy bb " +
            "inner join bms_business_type_relation br on bb.business_id = br.bms_business_id " +
            "where bb.status = 1 group by br.bms_type_id",nativeQuery = true)*/
    @Query(value = "select bl.id,br.bms_type_id from bms_dx_group_buy bb " +
            "inner join bms_business_type_relation br on bb.business_id = br.bms_business_id " +
            "inner join bms_business bu on bb.business_id = bu.id " +
            "inner join bms_business_label bl on bu.label = bl.label_name " +
            "where bb.status = 1 and bu.label is not null group by bu.id",nativeQuery = true)
    List<Map<String,Object>> selfFindHavingGroupBuyType();

    /*@Query(value = "select br.bms_type_id from bms_dx_group_buy bb " +
            "left join bms_business_type_relation br on bb.business_id = br.bms_business_id " +
            "left join bms_business bbu on bb.business_id = bbu.id " +
            "where bb.status = 1 and br.id is not null and IF(?1 != '',bbu.addr_city=?1 and IF(?2 != '',bbu.addr_district=?2,1=1),1=1) group by br.bms_type_id",nativeQuery = true)*/
    @Query(value = "select bl.id,br.bms_type_id from bms_dx_group_buy bb " +
            "inner join bms_business_type_relation br on bb.business_id = br.bms_business_id " +
            "inner join bms_business bu on bb.business_id = bu.id " +
            "inner join bms_business_label bl on bu.label = bl.label_name " +
            "where bb.status = 1 and IF(?1 != '',bu.addr_city=?1 and IF(?2 != '',bu.addr_district=?2,1=1),1=1) and bu.label is not null group by bu.id",nativeQuery = true)
    List<Map<String, Object>> selfFindHavingGroupBuyType(String addrCity, String addrDistinct);
}
